﻿using AppCommerciauxWinForm.Entity;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Threading.Tasks;
using System.IO;

namespace AppCommerciauxWinForm
{
    public class RequestBDD
    {

        private void initialisationBDDtest()
        {
            var dbFile = @"C:\livrable3\liv3.sqlite";
            if (File.Exists(dbFile))
            {


                TiersContact tc1 = new TiersContact("nom1", "prenom1", "fonction1", "mail1@mail.fr", "0288040506", "0689940302", new DateTime(2000, 04, 20), 1);
                TiersContact tc2 = new TiersContact("nom2", "prenom2", "fonction2", "mail2@mail.fr", "0203999908", "0605999302", new DateTime(1998, 04, 20), 6);
                TiersContact tc3 = new TiersContact("nom3", "prenom3", "fonction3", "mail3@mail.fr", "0203040506", "0605048882", new DateTime(1995, 04, 20), 8);
                SupprimerAllContacts();
                addContact(tc1);
                addContact(tc2);
                addContact(tc3);
                List<TiersContact> listContactTest = chargerListTiersContact();
                foreach (TiersContact tc in listContactTest)
                {
                    if (tc.Nom == "nom2")
                    {
                        tc.Prenom = "Prenom22";
                        setContact(tc);
                        //supprimerContact(tc.Id);
                    }
                }

                RendezVous rdv1 = new RendezVous(new DateTime(2015, 03, 08, 09, 15, 00), new DateTime(2015, 03, 08, 09, 45, 00), "commentaire 1", 2);
                RendezVous rdv2 = new RendezVous(new DateTime(2015, 03, 08, 11, 15, 00), new DateTime(2015, 03, 08, 11, 45, 00), "commentaire 2", 1);
                RendezVous rdv3 = new RendezVous(new DateTime(2015, 03, 08, 12, 15, 00), new DateTime(2015, 03, 08, 12, 45, 00), "commentaire 3", 3);
                SupprimerAllRDV();
                addRendezVous(rdv1);
                addRendezVous(rdv2);
                addRendezVous(rdv3);
                List<RendezVous> listRDVtest = chargerListRendezVous();
                foreach (RendezVous rdv in listRDVtest)
                {
                    rdv.infos_supp = "fbuiegyfuegrufgyeukge";
                    setRendezVous(rdv);

                    if (rdv.id_tiers_contact == 2)
                    {
                        RendezVous okok = getRendezVous(rdv.id);
                        //supprimerRDV(rdv.id);
                    }
                }


            }
        }


        private void chargerBDDSQLite()
        {
            var dbFile = ConfigurationManager.AppSettings["baseSQLite"];
            if (File.Exists(dbFile))
            {
                //connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                List<TiersContact> listContacts = chargerListTiersContact();

                foreach (TiersContact tc in listContacts)
                {
                    string t = tc.Nom;
                }
            }
        }


        #region RDV
        private List<RendezVous> chargerListRendezVous()
        {
            List<RendezVous> listRendezVous = new List<RendezVous>();

            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //read from the table
                        cmd.CommandText = @"SELECT id,date_debut,date_fin,infos_supp,id_tiers_contact FROM agenda";
                        using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int id = reader.GetInt32(0);
                                DateTime date_debut = reader.GetDateTime(1);
                                DateTime date_fin = reader.GetDateTime(2);
                                string infos_supp = reader.GetString(3);
                                int id_tiers_contact = reader.GetInt32(4);
                                RendezVous rdvRead = new RendezVous(id, date_debut, date_fin, infos_supp, id_tiers_contact);
                                listRendezVous.Add(rdvRead);
                            }
                        }
                        cmd.Dispose();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
            return listRendezVous;
        }
        private RendezVous getRendezVous(int IdRDV)
        {
            RendezVous RDV = new RendezVous();
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //read from the table
                        cmd.CommandText = @"SELECT id,date_debut,date_fin,infos_supp,id_tiers_contact FROM agenda where id = @id";
                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = IdRDV;
                        cmd.Parameters.Add(p1);
                        using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int id = reader.GetInt32(0);
                                DateTime date_debut = reader.GetDateTime(1);
                                DateTime date_fin = reader.GetDateTime(2);
                                string infos_supp = reader.GetString(3);
                                int id_tiers_contact = reader.GetInt32(4);
                                RDV = new RendezVous(id, date_debut, date_fin, infos_supp, id_tiers_contact);
                            }
                        }
                        cmd.Dispose();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
            return RDV;
        }
        private void addRendezVous(RendezVous rdv)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //parameterized insert
                        cmd.CommandText = @"INSERT INTO agenda (date_debut,date_fin,infos_supp,id_tiers_contact) VALUES(@date_debut,@date_fin,@infos_supp,@id_tiers_contact)";

                        //var p1 = cmd.CreateParameter();
                        //p1.ParameterName = "@id";
                        //p1.Value = 2;
                        var p2 = cmd.CreateParameter();
                        p2.ParameterName = "@date_debut";
                        p2.Value = rdv.date_debut;
                        var p3 = cmd.CreateParameter();
                        p3.ParameterName = "@date_fin";
                        p3.Value = rdv.date_fin;
                        var p4 = cmd.CreateParameter();
                        p4.ParameterName = "@infos_supp";
                        p4.Value = rdv.infos_supp;
                        var p5 = cmd.CreateParameter();
                        p5.ParameterName = "@id_tiers_contact";
                        p5.Value = rdv.id_tiers_contact;

                        //cmd.Parameters.Add(p1);
                        cmd.Parameters.Add(p2);
                        cmd.Parameters.Add(p3);
                        cmd.Parameters.Add(p4);
                        cmd.Parameters.Add(p5);

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void setRendezVous(RendezVous rdv)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //parameterized insert
                        cmd.CommandText = @"Update agenda set date_debut = @date_debut, date_fin = @date_fin, infos_supp = @infos_supp, id_tiers_contact = @id_tiers_contact where id = @id";

                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = rdv.id;
                        var p2 = cmd.CreateParameter();
                        p2.ParameterName = "@date_debut";
                        p2.Value = rdv.date_debut;
                        var p3 = cmd.CreateParameter();
                        p3.ParameterName = "@date_fin";
                        p3.Value = rdv.date_fin;
                        var p4 = cmd.CreateParameter();
                        p4.ParameterName = "@infos_supp";
                        p4.Value = rdv.infos_supp;
                        var p5 = cmd.CreateParameter();
                        p5.ParameterName = "@id_tiers_contact";
                        p5.Value = rdv.id_tiers_contact;

                        cmd.Parameters.Add(p1);
                        cmd.Parameters.Add(p2);
                        cmd.Parameters.Add(p3);
                        cmd.Parameters.Add(p4);
                        cmd.Parameters.Add(p5);

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void supprimerRDV(int IdRDV)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        cmd.CommandText = @"Delete FROM agenda where id = @id";
                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = IdRDV;
                        cmd.Parameters.Add(p1);
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void SupprimerAllRDV()
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        cmd.CommandText = @"Delete FROM agenda";
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        #endregion

        #region TiersContact
        private List<TiersContact> chargerListTiersContact()
        {
            List<TiersContact> listContacts = new List<TiersContact>();

            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //read from the table
                        cmd.CommandText = @"SELECT id,nom,prenom,fonction,mail,telfixe,telportable,datenaissance,id_tiers_societe FROM Tiers_contact";
                        using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int id = reader.GetInt32(0);
                                string nom = reader.GetString(1);
                                string prenom = reader.GetString(2);
                                string fonction = reader.GetString(3);
                                string mail = reader.GetString(4);
                                string telfixe = reader.GetString(5);
                                string telportable = reader.GetString(6);
                                DateTime datenaissance = reader.GetDateTime(7);
                                int id_tiers_societe = reader.GetInt32(8);
                                TiersContact tcRead = new TiersContact(id, nom, prenom, fonction, mail, telfixe, telportable, datenaissance, id_tiers_societe);
                                listContacts.Add(tcRead);
                            }
                        }
                        cmd.Dispose();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
            return listContacts;
        }
        private TiersContact getTiersContact(int IdTiersContact)
        {
            TiersContact TC = new TiersContact();
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //read from the table
                        cmd.CommandText = @"SELECT id,nom,prenom,fonction,mail,telfixe,telportable,datenaissance,id_tiers_societe FROM tiers_contact where id = @id";
                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = IdTiersContact;
                        cmd.Parameters.Add(p1);
                        using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int id = reader.GetInt32(0);
                                string nom = reader.GetString(1);
                                string prenom = reader.GetString(2);
                                string fonction = reader.GetString(3);
                                string mail = reader.GetString(4);
                                string telfixe = reader.GetString(5);
                                string telportable = reader.GetString(6);
                                DateTime datenaissance = reader.GetDateTime(7);
                                int id_tiers_societe = reader.GetInt32(8);

                                TC = new TiersContact(id, nom, prenom, fonction, mail, telfixe, telportable, datenaissance, id_tiers_societe);
                            }
                        }
                        cmd.Dispose();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
            return TC;
        }
        private void addContact(TiersContact tc)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //parameterized insert
                        cmd.CommandText = @"INSERT INTO Tiers_contact (nom,prenom,fonction,mail,telfixe,telportable,datenaissance,id_tiers_societe) VALUES(@nom,@prenom,@fonction,@mail,@telfixe,@telportable,@datenaissance,@id_tiers_societe)";

                        //var p1 = cmd.CreateParameter();
                        //p1.ParameterName = "@id";
                        //p1.Value = 2;
                        var p2 = cmd.CreateParameter();
                        p2.ParameterName = "@nom";
                        p2.Value = tc.Nom;
                        var p3 = cmd.CreateParameter();
                        p3.ParameterName = "@prenom";
                        p3.Value = tc.Prenom;
                        var p4 = cmd.CreateParameter();
                        p4.ParameterName = "@fonction";
                        p4.Value = tc.Fonction;
                        var p5 = cmd.CreateParameter();
                        p5.ParameterName = "@mail";
                        p5.Value = tc.Mail;
                        var p6 = cmd.CreateParameter();
                        p6.ParameterName = "@telfixe";
                        p6.Value = tc.TelFixe;
                        var p7 = cmd.CreateParameter();
                        p7.ParameterName = "@telportable";
                        p7.Value = tc.TelPortable;
                        var p8 = cmd.CreateParameter();
                        p8.ParameterName = "@datenaissance";
                        p8.Value = tc.DateNaissance;
                        var p9 = cmd.CreateParameter();
                        p9.ParameterName = "@id_tiers_societe";
                        p9.Value = tc.IdTiersSociete;

                        //cmd.Parameters.Add(p1);
                        cmd.Parameters.Add(p2);
                        cmd.Parameters.Add(p3);
                        cmd.Parameters.Add(p4);
                        cmd.Parameters.Add(p5);
                        cmd.Parameters.Add(p6);
                        cmd.Parameters.Add(p7);
                        cmd.Parameters.Add(p8);
                        cmd.Parameters.Add(p9);

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void setContact(TiersContact tc)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //parameterized insert
                        cmd.CommandText = @"Update Tiers_contact set nom = @nom, prenom = @prenom, fonction = @fonction,mail = @mail, telfixe = @telfixe, telportable = @telportable, datenaissance = @datenaissance, id_tiers_societe = @id_tiers_societe where id = @id";

                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = tc.Id;
                        var p2 = cmd.CreateParameter();
                        p2.ParameterName = "@nom";
                        p2.Value = tc.Nom;
                        var p3 = cmd.CreateParameter();
                        p3.ParameterName = "@prenom";
                        p3.Value = tc.Prenom;
                        var p4 = cmd.CreateParameter();
                        p4.ParameterName = "@fonction";
                        p4.Value = tc.Fonction;
                        var p5 = cmd.CreateParameter();
                        p5.ParameterName = "@mail";
                        p5.Value = tc.Mail;
                        var p6 = cmd.CreateParameter();
                        p6.ParameterName = "@telfixe";
                        p6.Value = tc.TelFixe;
                        var p7 = cmd.CreateParameter();
                        p7.ParameterName = "@telportable";
                        p7.Value = tc.TelPortable;
                        var p8 = cmd.CreateParameter();
                        p8.ParameterName = "@datenaissance";
                        p8.Value = tc.DateNaissance;
                        var p9 = cmd.CreateParameter();
                        p9.ParameterName = "@id_tiers_societe";
                        p9.Value = tc.IdTiersSociete;

                        cmd.Parameters.Add(p1);
                        cmd.Parameters.Add(p2);
                        cmd.Parameters.Add(p3);
                        cmd.Parameters.Add(p4);
                        cmd.Parameters.Add(p5);
                        cmd.Parameters.Add(p6);
                        cmd.Parameters.Add(p7);
                        cmd.Parameters.Add(p8);
                        cmd.Parameters.Add(p9);

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void supprimerContact(int IdContact)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        cmd.CommandText = @"Delete FROM Tiers_contact where id = @id";
                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = IdContact;
                        cmd.Parameters.Add(p1);
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void SupprimerAllContacts()
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        cmd.CommandText = @"Delete FROM Tiers_contact";
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        #endregion

        #region TiersSociete
        private List<TiersSociete> chargerListTiersSociete()
        {
            List<TiersSociete> listSocietes = new List<TiersSociete>();

            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //read from the table
                        cmd.CommandText = @"SELECT id,raisonSociale,rue,cp,ville,tel,fax,siret,logo,remise FROM Tiers_societe";
                        using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int id = reader.GetInt32(0);
                                string raisonSociale = reader.GetString(1);
                                string rue = reader.GetString(2);
                                string cp = reader.GetString(3);
                                string ville = reader.GetString(4);
                                string tel = reader.GetString(5);
                                string fax = reader.GetString(6);
                                string siret = reader.GetString(7);
                                string logo = reader.GetString(8);
                                int remise = reader.GetInt32(9);
                                TiersSociete tsRead = new TiersSociete(id, raisonSociale, rue, cp, ville, tel, fax, siret, logo, remise);
                                listSocietes.Add(tsRead);
                            }
                        }
                        cmd.Dispose();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
            return listSocietes;
        }
        private TiersSociete getTiersSociete(int IdTiersSociete)
        {
            TiersSociete TS = new TiersSociete();
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //read from the table
                        cmd.CommandText = @"SELECT id,raisonSociale,rue,cp,ville,tel,fax,siret,logo,remise FROM Tiers_societe where id = @id";
                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = IdTiersSociete;
                        cmd.Parameters.Add(p1);
                        using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int id = reader.GetInt32(0);
                                string raisonSociale = reader.GetString(1);
                                string rue = reader.GetString(2);
                                string cp = reader.GetString(3);
                                string ville = reader.GetString(4);
                                string tel = reader.GetString(5);
                                string fax = reader.GetString(6);
                                string siret = reader.GetString(7);
                                string logo = reader.GetString(8);
                                int remise = reader.GetInt32(9);

                                TS = new TiersSociete(id, raisonSociale, rue, cp, ville, tel, fax, siret, logo, remise);
                            }
                        }
                        cmd.Dispose();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
            return TS;
        }
        private void addSociete(TiersSociete ts)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //parameterized insert
                        cmd.CommandText = @"INSERT INTO Tiers_societe (raisonSociale,rue,cp,ville,tel,fax,siret,logo,remise) VALUES(@raisonSociale,@rue,@cp,@ville,@tel,@fax,@siret,@logo,@remise)";

                        //var p1 = cmd.CreateParameter();
                        //p1.ParameterName = "@id";
                        //p1.Value = 2;
                        var p2 = cmd.CreateParameter();
                        p2.ParameterName = "@raisonSociale";
                        p2.Value = ts.RaisonSociale;
                        var p3 = cmd.CreateParameter();
                        p3.ParameterName = "@rue";
                        p3.Value = ts.Rue;
                        var p4 = cmd.CreateParameter();
                        p4.ParameterName = "@cp";
                        p4.Value = ts.Cp;
                        var p5 = cmd.CreateParameter();
                        p5.ParameterName = "@ville";
                        p5.Value = ts.Ville;
                        var p6 = cmd.CreateParameter();
                        p6.ParameterName = "@tel";
                        p6.Value = ts.Tel;
                        var p7 = cmd.CreateParameter();
                        p7.ParameterName = "@fax";
                        p7.Value = ts.Fax;
                        var p8 = cmd.CreateParameter();
                        p8.ParameterName = "@siret";
                        p8.Value = ts.Siret;
                        var p9 = cmd.CreateParameter();
                        p9.ParameterName = "@logo";
                        p9.Value = ts.Logo;
                        var p10 = cmd.CreateParameter();
                        p10.ParameterName = "@remise";
                        p10.Value = ts.Remise;

                        //cmd.Parameters.Add(p1);
                        cmd.Parameters.Add(p2);
                        cmd.Parameters.Add(p3);
                        cmd.Parameters.Add(p4);
                        cmd.Parameters.Add(p5);
                        cmd.Parameters.Add(p6);
                        cmd.Parameters.Add(p7);
                        cmd.Parameters.Add(p8);
                        cmd.Parameters.Add(p9);
                        cmd.Parameters.Add(p10);

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void setSociete(TiersSociete ts)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //parameterized insert
                        cmd.CommandText = @"Update Tiers_societe set raisonSociale = @raisonSociale, rue = @rue, cp = @cp,ville = @ville, tel = @tel, fax = @fax, siret = @siret, logo = @logo, remise = @remise where id = @id";

                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = ts.Id;
                        var p2 = cmd.CreateParameter();
                        p2.ParameterName = "@raisonSociale";
                        p2.Value = ts.RaisonSociale;
                        var p3 = cmd.CreateParameter();
                        p3.ParameterName = "@rue";
                        p3.Value = ts.Rue;
                        var p4 = cmd.CreateParameter();
                        p4.ParameterName = "@cp";
                        p4.Value = ts.Cp;
                        var p5 = cmd.CreateParameter();
                        p5.ParameterName = "@ville";
                        p5.Value = ts.Ville;
                        var p6 = cmd.CreateParameter();
                        p6.ParameterName = "@tel";
                        p6.Value = ts.Tel;
                        var p7 = cmd.CreateParameter();
                        p7.ParameterName = "@fax";
                        p7.Value = ts.Fax;
                        var p8 = cmd.CreateParameter();
                        p8.ParameterName = "@siret";
                        p8.Value = ts.Siret;
                        var p9 = cmd.CreateParameter();
                        p9.ParameterName = "@logo";
                        p9.Value = ts.Logo;
                        var p10 = cmd.CreateParameter();
                        p10.ParameterName = "@remise";
                        p10.Value = ts.Remise;

                        cmd.Parameters.Add(p1);
                        cmd.Parameters.Add(p2);
                        cmd.Parameters.Add(p3);
                        cmd.Parameters.Add(p4);
                        cmd.Parameters.Add(p5);
                        cmd.Parameters.Add(p6);
                        cmd.Parameters.Add(p7);
                        cmd.Parameters.Add(p8);
                        cmd.Parameters.Add(p9);
                        cmd.Parameters.Add(p10);

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void supprimerSociete(int IdSociete)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        cmd.CommandText = @"Delete FROM Tiers_societe where id = @id";
                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = IdSociete;
                        cmd.Parameters.Add(p1);
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void SupprimerAllSocietes()
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        cmd.CommandText = @"Delete FROM Tiers_societe";
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        #endregion

        #region CommandeClient
        private List<CommandeClient> chargerListCommandeClient()
        {
            List<CommandeClient> listCommandeClient = new List<CommandeClient>();

            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //read from the table
                        cmd.CommandText = @"SELECT id,dateCreation,statut,totalHT,totalTTC,montantAcompte,montantTva,montantRemise,id_tiers_societe,id_tiers_contact FROM commandeclient";
                        using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int id = reader.GetInt32(0);
                                DateTime dateCreation = reader.GetDateTime(1);
                                int statut = reader.GetInt32(2);
                                int totalHT = reader.GetInt32(3);
                                int totalTTC = reader.GetInt32(4);
                                int montantAcompte = reader.GetInt32(5);
                                int montantTva = reader.GetInt32(6);
                                int montantRemise = reader.GetInt32(7);
                                int id_tiers_societe = reader.GetInt32(8);
                                int id_tiers_contact = reader.GetInt32(9);
                                CommandeClient ccRead = new CommandeClient(id, dateCreation, statut, totalHT, totalTTC, montantAcompte, montantTva, montantRemise, id_tiers_societe, id_tiers_contact);
                                listCommandeClient.Add(ccRead);
                            }
                        }
                        cmd.Dispose();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
            return listCommandeClient;
        }
        private CommandeClient getCommandeClient(int IdCommandeClient)
        {
            CommandeClient CC = new CommandeClient();
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //read from the table
                        cmd.CommandText = @"SELECT id,dateCreation,statut,totalHT,totalTTC,montantAcompte,montantTva,montantRemise,id_tiers_societe,id_tiers_contact FROM commandeclient where id = @id";
                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = IdCommandeClient;
                        cmd.Parameters.Add(p1);
                        using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int id = reader.GetInt32(0);
                                DateTime dateCreation = reader.GetDateTime(1);
                                int statut = reader.GetInt32(2);
                                int totalHT = reader.GetInt32(3);
                                int totalTTC = reader.GetInt32(4);
                                int montantAcompte = reader.GetInt32(5);
                                int montantTva = reader.GetInt32(6);
                                int montantRemise = reader.GetInt32(7);
                                int id_tiers_societe = reader.GetInt32(8);
                                int id_tiers_contact = reader.GetInt32(9);

                                CC = new CommandeClient(id, dateCreation, statut, totalHT, totalTTC, montantAcompte, montantTva, montantRemise, id_tiers_societe, id_tiers_contact);
                            }
                        }
                        cmd.Dispose();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
            return CC;
        }
        private void addCommandeClient(CommandeClient cc)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //parameterized insert
                        cmd.CommandText = @"INSERT INTO commandeClient (dateCreation,statut,totalHT,totalTTC,montantAcompte,montantTva,montantRemise,id_tiers_societe,id_tiers_contact) VALUES(@dateCreation,@statut,@totalHT,@totalTTC,@montantAcompte,@montantTva,@montantRemise,@id_tiers_societe,@id_tiers_contact)";

                        //var p1 = cmd.CreateParameter();
                        //p1.ParameterName = "@id";
                        //p1.Value = 2;
                        var p2 = cmd.CreateParameter();
                        p2.ParameterName = "@dateCreation";
                        p2.Value = cc.dateCreation;
                        var p3 = cmd.CreateParameter();
                        p3.ParameterName = "@statut";
                        p3.Value = cc.Statut;
                        var p4 = cmd.CreateParameter();
                        p4.ParameterName = "@totalHT";
                        p4.Value = cc.TotalHT;
                        var p5 = cmd.CreateParameter();
                        p5.ParameterName = "@totalTTC";
                        p5.Value = cc.TotalTTC;
                        var p6 = cmd.CreateParameter();
                        p6.ParameterName = "@montantAcompte";
                        p6.Value = cc.montantAcompte;
                        var p7 = cmd.CreateParameter();
                        p7.ParameterName = "@montantTva";
                        p7.Value = cc.montantTva;
                        var p8 = cmd.CreateParameter();
                        p8.ParameterName = "@montantRemise";
                        p8.Value = cc.montantRemise;
                        var p9 = cmd.CreateParameter();
                        p9.ParameterName = "@id_tiers_societe";
                        p9.Value = cc.Id_TiersSociete;
                        var p10 = cmd.CreateParameter();
                        p10.ParameterName = "@id_tiers_contact";
                        p10.Value = cc.Id_TiersContact;

                        //cmd.Parameters.Add(p1);
                        cmd.Parameters.Add(p2);
                        cmd.Parameters.Add(p3);
                        cmd.Parameters.Add(p4);
                        cmd.Parameters.Add(p5);
                        cmd.Parameters.Add(p6);
                        cmd.Parameters.Add(p7);
                        cmd.Parameters.Add(p8);
                        cmd.Parameters.Add(p9);
                        cmd.Parameters.Add(p10);

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void setCommandeClient(CommandeClient cc)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //parameterized insert
                        cmd.CommandText = @"Update commandeClient set dateCreation = @dateCreation, statut = @statut, totalHT = @totalHT,totalTTC = @totalTTC, montantAcompte = @montantAcompte, montantTva = @montantTva, montantRemise = @montantRemise, id_tiers_societe = @id_tiers_societe, id_tiers_contact = @id_tiers_contact where id = @id";

                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = cc.Id;
                        var p2 = cmd.CreateParameter();
                        p2.ParameterName = "@dateCreation";
                        p2.Value = cc.dateCreation;
                        var p3 = cmd.CreateParameter();
                        p3.ParameterName = "@statut";
                        p3.Value = cc.Statut;
                        var p4 = cmd.CreateParameter();
                        p4.ParameterName = "@totalHT";
                        p4.Value = cc.TotalHT;
                        var p5 = cmd.CreateParameter();
                        p5.ParameterName = "@totalTTC";
                        p5.Value = cc.TotalTTC;
                        var p6 = cmd.CreateParameter();
                        p6.ParameterName = "@montantAcompte";
                        p6.Value = cc.montantAcompte;
                        var p7 = cmd.CreateParameter();
                        p7.ParameterName = "@montantTva";
                        p7.Value = cc.montantTva;
                        var p8 = cmd.CreateParameter();
                        p8.ParameterName = "@montantRemise";
                        p8.Value = cc.montantRemise;
                        var p9 = cmd.CreateParameter();
                        p9.ParameterName = "@id_tiers_societe";
                        p9.Value = cc.Id_TiersSociete;
                        var p10 = cmd.CreateParameter();
                        p10.ParameterName = "@id_tiers_contact";
                        p10.Value = cc.Id_TiersContact;

                        cmd.Parameters.Add(p1);
                        cmd.Parameters.Add(p2);
                        cmd.Parameters.Add(p3);
                        cmd.Parameters.Add(p4);
                        cmd.Parameters.Add(p5);
                        cmd.Parameters.Add(p6);
                        cmd.Parameters.Add(p7);
                        cmd.Parameters.Add(p8);
                        cmd.Parameters.Add(p9);
                        cmd.Parameters.Add(p10);

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void supprimerCommandeClient(int IdCommandeClient)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        cmd.CommandText = @"Delete FROM commandeClient where id = @id";
                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = IdCommandeClient;
                        cmd.Parameters.Add(p1);
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void SupprimerAllCommandeClient()
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        cmd.CommandText = @"Delete FROM commandeClient";
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        #endregion

        #region LigneCommandeClient
        private List<LigneCommandeClient> chargerListLigneCommandeClient()
        {
            List<LigneCommandeClient> listLigneCommandeClient = new List<LigneCommandeClient>();

            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //read from the table
                        cmd.CommandText = @"SELECT id,quantite,puHT,tauxTva,totalHT,totalTTC,id_commandeclient,id_produit FROM lignecommandeclient";
                        using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int id = reader.GetInt32(0);
                                int quantite = reader.GetInt32(1);
                                int puHT = reader.GetInt32(2);
                                int tauxTva = reader.GetInt32(3);
                                int totalHT = reader.GetInt32(4);
                                int totalTTC = reader.GetInt32(5);
                                int id_commandeclient = reader.GetInt32(6);
                                int id_produit = reader.GetInt32(7);

                                LigneCommandeClient lccRead = new LigneCommandeClient(id, quantite, puHT, tauxTva, totalHT, totalTTC, id_commandeclient, id_produit);
                                listLigneCommandeClient.Add(lccRead);
                            }
                        }
                        cmd.Dispose();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
            return listLigneCommandeClient;
        }
        private LigneCommandeClient getLigneCommandeClient(int IdLigneCommandeClient)
        {
            LigneCommandeClient LCC = new LigneCommandeClient();
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //read from the table
                        cmd.CommandText = @"SELECT id,quantite,puHT,tauxTva,totalHT,totalTTC,id_commandeclient,id_produit FROM lignecommandeclient where id = @id";
                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = IdLigneCommandeClient;
                        cmd.Parameters.Add(p1);
                        using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int id = reader.GetInt32(0);
                                int quantite = reader.GetInt32(1);
                                int puHT = reader.GetInt32(2);
                                int tauxTva = reader.GetInt32(3);
                                int totalHT = reader.GetInt32(4);
                                int totalTTC = reader.GetInt32(5);
                                int id_commandeclient = reader.GetInt32(6);
                                int id_produit = reader.GetInt32(7);

                                LCC = new LigneCommandeClient(id, quantite, puHT, tauxTva, totalHT, totalTTC, id_commandeclient, id_produit);
                            }
                        }
                        cmd.Dispose();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
            return LCC;
        }
        private void addLigneCommandeClient(LigneCommandeClient lcc)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //parameterized insert
                        cmd.CommandText = @"INSERT INTO lignecommandeclient (quantite,puHT,tauxTva,totalHT,totalTTC,id_commandeclient,id_produit) VALUES(@quantite,@puHT,@tauxTva,@totalHT,@totalTTC,@id_commandeclient,@id_produit)";

                        //var p1 = cmd.CreateParameter();
                        //p1.ParameterName = "@id";
                        //p1.Value = 2;
                        var p2 = cmd.CreateParameter();
                        p2.ParameterName = "@quantite";
                        p2.Value = lcc.Quantite;
                        var p3 = cmd.CreateParameter();
                        p3.ParameterName = "@puHT";
                        p3.Value = lcc.puHT;
                        var p4 = cmd.CreateParameter();
                        p4.ParameterName = "@tauxTva";
                        p4.Value = lcc.TauxTva;
                        var p5 = cmd.CreateParameter();
                        p5.ParameterName = "@totalHT";
                        p5.Value = lcc.TotalHT;
                        var p6 = cmd.CreateParameter();
                        p6.ParameterName = "@totalTTC";
                        p6.Value = lcc.TotalTTC;
                        var p7 = cmd.CreateParameter();
                        p7.ParameterName = "@id_commandeclient";
                        p7.Value = lcc.Id_CommandeClient;
                        var p8 = cmd.CreateParameter();
                        p8.ParameterName = "@id_produit";
                        p8.Value = lcc.Id_Produit;


                        //cmd.Parameters.Add(p1);
                        cmd.Parameters.Add(p2);
                        cmd.Parameters.Add(p3);
                        cmd.Parameters.Add(p4);
                        cmd.Parameters.Add(p5);
                        cmd.Parameters.Add(p6);
                        cmd.Parameters.Add(p7);
                        cmd.Parameters.Add(p8);

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void setLigneCommandeClient(LigneCommandeClient lcc)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //parameterized insert
                        cmd.CommandText = @"Update lignecommandelient set quantite = @quantite, puHT = @puHT, tauxTva = @tauxTva,totalHT = @totalHT, totalTTC = @totalTTC, id_commandeclient = @id_commandeclient, id_produit = @id_produit where id = @id";

                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = lcc.Id;
                        var p2 = cmd.CreateParameter();
                        p2.ParameterName = "@quantite";
                        p2.Value = lcc.Quantite;
                        var p3 = cmd.CreateParameter();
                        p3.ParameterName = "@puHT";
                        p3.Value = lcc.puHT;
                        var p4 = cmd.CreateParameter();
                        p4.ParameterName = "@tauxTva";
                        p4.Value = lcc.TauxTva;
                        var p5 = cmd.CreateParameter();
                        p5.ParameterName = "@totalHT";
                        p5.Value = lcc.TotalHT;
                        var p6 = cmd.CreateParameter();
                        p6.ParameterName = "@totalTTC";
                        p6.Value = lcc.TotalTTC;
                        var p7 = cmd.CreateParameter();
                        p7.ParameterName = "@id_commandeclient";
                        p7.Value = lcc.Id_CommandeClient;
                        var p8 = cmd.CreateParameter();
                        p8.ParameterName = "@id_produit";
                        p8.Value = lcc.Id_Produit;

                        cmd.Parameters.Add(p1);
                        cmd.Parameters.Add(p2);
                        cmd.Parameters.Add(p3);
                        cmd.Parameters.Add(p4);
                        cmd.Parameters.Add(p5);
                        cmd.Parameters.Add(p6);
                        cmd.Parameters.Add(p7);
                        cmd.Parameters.Add(p8);

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void supprimerLigneCommandeClient(int IdLigneCommandeClient)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        cmd.CommandText = @"Delete FROM lignecommandeclient where id = @id";
                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = IdLigneCommandeClient;
                        cmd.Parameters.Add(p1);
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void SupprimerAllLigneCommandeClient()
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        cmd.CommandText = @"Delete FROM lignecommandeclient";
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        #endregion

        #region Produit
        private List<Produit> chargerListProduit()
        {
            List<Produit> listProduit = new List<Produit>();

            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //read from the table
                        cmd.CommandText = @"SELECT id,libelle,pu,id_unite,id_tva FROM produit";
                        using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int id = reader.GetInt32(0);
                                string libelle = reader.GetString(1);
                                float pu = reader.GetInt32(2);
                                int id_unite = reader.GetInt32(3);
                                int id_tva = reader.GetInt32(4);

                                Produit pdtRead = new Produit(id, libelle, pu, id_unite, id_tva);
                                listProduit.Add(pdtRead);
                            }
                        }
                        cmd.Dispose();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
            return listProduit;
        }
        private Produit getProduit(int IdProduit)
        {
            Produit PDT = new Produit();
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //read from the table
                        cmd.CommandText = @"SELECT id,libelle,pu,id_unite,id_tva FROM produit where id = @id";
                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = IdProduit;
                        cmd.Parameters.Add(p1);
                        using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int id = reader.GetInt32(0);
                                string libelle = reader.GetString(1);
                                float pu = reader.GetInt32(2);
                                int id_unite = reader.GetInt32(3);
                                int id_tva = reader.GetInt32(4);

                                PDT = new Produit(id, libelle, pu, id_unite, id_tva);
                            }
                        }
                        cmd.Dispose();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
            return PDT;
        }
        private void addProduit(Produit pdt)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //parameterized insert
                        cmd.CommandText = @"INSERT INTO produit (libelle,pu,id_unite,id_tva) VALUES(@libelle,@pu,@id_unite,@id_tva)";

                        //var p1 = cmd.CreateParameter();
                        //p1.ParameterName = "@id";
                        //p1.Value = 2;
                        var p2 = cmd.CreateParameter();
                        p2.ParameterName = "@libelle";
                        p2.Value = pdt.Libelle;
                        var p3 = cmd.CreateParameter();
                        p3.ParameterName = "@pu";
                        p3.Value = pdt.PrixUnitaire;
                        var p4 = cmd.CreateParameter();
                        p4.ParameterName = "@id_unite";
                        p4.Value = pdt.Id_Unite;
                        var p5 = cmd.CreateParameter();
                        p5.ParameterName = "@id_tva";
                        p5.Value = pdt.Id_Tva;

                        //cmd.Parameters.Add(p1);
                        cmd.Parameters.Add(p2);
                        cmd.Parameters.Add(p3);
                        cmd.Parameters.Add(p4);
                        cmd.Parameters.Add(p5);

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void setProduit(Produit pdt)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //parameterized insert
                        cmd.CommandText = @"Update produit set libelle = @libelle, pu = @pu, id_unite = @id_unite,id_tva = @id_tva where id = @id";

                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = pdt.Id;
                        var p2 = cmd.CreateParameter();
                        p2.ParameterName = "@libelle";
                        p2.Value = pdt.Libelle;
                        var p3 = cmd.CreateParameter();
                        p3.ParameterName = "@pu";
                        p3.Value = pdt.PrixUnitaire;
                        var p4 = cmd.CreateParameter();
                        p4.ParameterName = "@id_unite";
                        p4.Value = pdt.Id_Unite;
                        var p5 = cmd.CreateParameter();
                        p5.ParameterName = "@id_tva";
                        p5.Value = pdt.Id_Tva;

                        cmd.Parameters.Add(p1);
                        cmd.Parameters.Add(p2);
                        cmd.Parameters.Add(p3);
                        cmd.Parameters.Add(p4);
                        cmd.Parameters.Add(p5);

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void supprimerProduit(int IdProduit)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        cmd.CommandText = @"Delete FROM produit where id = @id";
                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = IdProduit;
                        cmd.Parameters.Add(p1);
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void SupprimerAllProduit()
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        cmd.CommandText = @"Delete FROM produit";
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        #endregion

        #region TVA
        private List<Tva> chargerListTva()
        {
            List<Tva> listTva = new List<Tva>();

            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //read from the table
                        cmd.CommandText = @"SELECT id,Taux FROM tva";
                        using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int id = reader.GetInt32(0);
                                float Taux = reader.GetInt32(1);

                                Tva tvaRead = new Tva(id, Taux);
                                listTva.Add(tvaRead);
                            }
                        }
                        cmd.Dispose();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
            return listTva;
        }
        private Tva getTva(int IdTva)
        {
            Tva TVA = new Tva();
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //read from the table
                        cmd.CommandText = @"SELECT id,Taux FROM tva where id = @id";
                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = IdTva;
                        cmd.Parameters.Add(p1);
                        using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int id = reader.GetInt32(0);
                                float Taux = reader.GetInt32(1);

                                TVA = new Tva(id, Taux);
                            }
                        }
                        cmd.Dispose();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
            return TVA;
        }
        private void addTva(Tva tva)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //parameterized insert
                        cmd.CommandText = @"INSERT INTO tva (Taux) VALUES(@Taux)";

                        //var p1 = cmd.CreateParameter();
                        //p1.ParameterName = "@id";
                        //p1.Value = 2;
                        var p2 = cmd.CreateParameter();
                        p2.ParameterName = "@Taux";
                        p2.Value = tva.Taux;

                        //cmd.Parameters.Add(p1);
                        cmd.Parameters.Add(p2);

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void setTva(Tva tva)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //parameterized insert
                        cmd.CommandText = @"Update produit set Taux = @Taux where id = @id";

                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = tva.Id;
                        var p2 = cmd.CreateParameter();
                        p2.ParameterName = "@Taux";
                        p2.Value = tva.Taux;

                        cmd.Parameters.Add(p1);
                        cmd.Parameters.Add(p2);

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void supprimerTva(int IdTva)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        cmd.CommandText = @"Delete FROM tva where id = @id";
                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = IdTva;
                        cmd.Parameters.Add(p1);
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void SupprimerAllTva()
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        cmd.CommandText = @"Delete FROM tva";
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        #endregion

        #region Unite
        private List<Unite> chargerListUnite()
        {
            List<Unite> listUnite = new List<Unite>();

            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //read from the table
                        cmd.CommandText = @"SELECT id, NomUnite FROM unite";
                        using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int id = reader.GetInt32(0);
                                string NomUnite = reader.GetString(1);

                                Unite uniteRead = new Unite(id, NomUnite);
                                listUnite.Add(uniteRead);
                            }
                        }
                        cmd.Dispose();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
            return listUnite;
        }
        private Unite getUnite(int IdUnite)
        {
            Unite UNT = new Unite();
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //read from the table
                        cmd.CommandText = @"SELECT id,NomUnite FROM unite where id = @id";
                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = IdUnite;
                        cmd.Parameters.Add(p1);
                        using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int id = reader.GetInt32(0);
                                string nomUnite = reader.GetString(1);

                                UNT = new Unite(id, nomUnite);
                            }
                        }
                        cmd.Dispose();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
            return UNT;
        }
        private void addUnite(Unite unt)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //parameterized insert
                        cmd.CommandText = @"INSERT INTO unite (NomUnite) VALUES(@nomUnite)";

                        //var p1 = cmd.CreateParameter();
                        //p1.ParameterName = "@id";
                        //p1.Value = 2;
                        var p2 = cmd.CreateParameter();
                        p2.ParameterName = "@nomUnite";
                        p2.Value = unt.Nom;

                        //cmd.Parameters.Add(p1);
                        cmd.Parameters.Add(p2);

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void setUnite(Unite unt)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //parameterized insert
                        cmd.CommandText = @"Update produit set NomUnite = @nomUnite where id = @id";

                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = unt.Id;
                        var p2 = cmd.CreateParameter();
                        p2.ParameterName = "@nomUnite";
                        p2.Value = unt.Nom;

                        cmd.Parameters.Add(p1);
                        cmd.Parameters.Add(p2);

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void supprimerUnite(int IdUnite)
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        cmd.CommandText = @"Delete FROM unite where id = @id";
                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = IdUnite;
                        cmd.Parameters.Add(p1);
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        private void SupprimerAllUnite()
        {
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        cmd.CommandText = @"Delete FROM unite";
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
        }
        #endregion

        #region Ville
        private List<Ville> chargerListVille()
        {
            List<Ville> listVille = new List<Ville>();

            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //read from the table
                        cmd.CommandText = @"SELECT ville_id,ville_nom_departement,ville_code_postal FROM france_villes";
                        using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int id = reader.GetInt32(0);
                                string nom_departement = reader.GetString(1);
                                string code_postal = reader.GetString(2);

                                Ville vilRead = new Ville(id, nom_departement, code_postal);
                                listVille.Add(vilRead);
                            }
                        }
                        cmd.Dispose();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
            return listVille;
        }
        private Ville getVille(int IdVille)
        {
            Ville VIL = new Ville();
            using (var factory = new System.Data.SQLite.SQLiteFactory())
            using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
            {
                dbConn.ConnectionString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", ConfigurationManager.AppSettings["baseSQLite"]);
                dbConn.Open();
                using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
                {
                    try
                    {
                        //read from the table
                        cmd.CommandText = @"SELECT ville_id,ville_nom_departement,ville_code_postal FROM france_villes where id = @id";
                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "@id";
                        p1.Value = IdVille;
                        cmd.Parameters.Add(p1);
                        using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int id = reader.GetInt32(0);
                                string nom_departement = reader.GetString(1);
                                string code_postal = reader.GetString(2);

                                VIL = new Ville(id, nom_departement, code_postal);
                            }
                        }
                        cmd.Dispose();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                }
                if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
                dbConn.Dispose();
                factory.Dispose();
            }
            return VIL;
        }
        #endregion
    }
}
